﻿using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;

namespace POC_001
{
    class Program
    {
        private static string excelFile = @"C:\Sandbox\PTE\Common\PatternE.xlsx";
        private static string sheetName = "Sheet1";
        private static string columnRegex = "[0-9]";

        static void Main(string[] args)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelFile, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();

                Sheet detailSheet = sheets.Single(p => p.Name.Value.Equals(sheetName));

                WorksheetPart detailWorksheetPart = (WorksheetPart)workbookPart.GetPartById(detailSheet.Id);

                Row lastRow = detailWorksheetPart.Worksheet.Descendants<Row>().LastOrDefault();
                Row firstRow = detailWorksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();

                SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;

                //Console.WriteLine("First Row");

                //if (firstRow != null)
                //{
                //    foreach (Cell c in firstRow.ChildElements)
                //    {
                //        string value = GetValue(c, stringTablePart);
                //        Console.WriteLine("{0} : {1}", c.CellReference, value);
                //        string columnReference = Regex.Replace(c.CellReference, columnRegex, "");
                //        Console.WriteLine("Column : {0}", columnReference);
                //    }
                //}

                //Console.WriteLine("Last Row");

                //if (lastRow != null)
                //{
                //    foreach (Cell c in lastRow.ChildElements)
                //    {
                //        string value = GetValue(c, stringTablePart);
                //        Console.WriteLine("{0} : {1}", c.CellReference, value);
                //        string columnReference = Regex.Replace(c.CellReference, columnRegex, "");
                //        Console.WriteLine("Column : {0}", columnReference);
                //    }
                //}

                IEnumerable<Row> rows = detailWorksheetPart.Worksheet.Descendants<Row>();

                foreach(Row r in rows)
                {
                    if (r != null)
                    {
                        Console.WriteLine("Row Index : {0}", r.RowIndex.ToString());
                        foreach (Cell c in r.ChildElements)
                        {
                            //string value = GetValue(spreadsheetDocument, c, stringTablePart);
                            string value = GetValue(c, stringTablePart);
                            Console.WriteLine("{0} : {1}", c.CellReference, value);
                            string columnReference = Regex.Replace(c.CellReference, columnRegex, "");
                            Console.WriteLine("Column : {0}", columnReference);
                        }
                        Console.WriteLine("----end row----");
                    }
                }
            }
        }

        public static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
        {

            if (cell.ChildElements.Count == 0) return null;
            //FARUQ: Need to check the elements, if there's formula, child element count is 2, the result of that formula at the last index.
            //get cell value
            string value = cell.ElementAt(0).InnerText;

            //Look up real value from shared string table
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            
            return value;

        }

        public static string GetValue(SpreadsheetDocument document, Cell cell, SharedStringTablePart stringTablePart)
        {
            try
            {
                if (cell.ChildElements.Count == 0) return null;
                //FARUQ: Need to check the elements, if there's formula, child element count is 2, the result of that formula at the last index.
                //get cell value
                string value = cell.ElementAt(0).InnerText;
                CellFormat cellFormat = (CellFormat)document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(int.Parse(cell.StyleIndex));
                

                string format =
                    document.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>().First(p => p.NumberFormatId.ToString() == cellFormat.NumberFormatId.ToString()).FormatCode;

                
                double d = double.Parse(cell.CellValue.InnerText);
                string val = d.ToString(format);

                //Look up real value from shared string table
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                    value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

                return value;
            }
            catch (Exception ex)
            {
                
            }
            return null;
        }
    }
}
